package com.zerowidth.db.op;

import com.zerowidth.db.DBHelper;
import com.zerowidth.db.base.SqlResult;
import com.zerowidth.db.bean.CollectBean;
import com.zerowidth.db.utils.StringUtils;
import com.zerowidth.db.utils.TimeUtils;

import java.util.ArrayList;
import java.util.List;

public class CollectDB {

    public static int count(String start_time, String end_time) {
        String sql = String.format("SELECT COUNT(*) FROM moc_collect WHERE create_time >= '%s' " +
                " AND create_time <= '%s'", start_time, end_time);

        return DBHelper.queryCount(sql);
    }

    public static int countUser(String start_time, String end_time) {
        String sql = String.format("SELECT COUNT(*) FROM (SELECT COUNT(*) FROM moc_collect WHERE create_time >= '%s' " +
                " AND create_time <= '%s' GROUP BY stat_id) t", start_time, end_time);

        return DBHelper.queryCount(sql);
    }

    public static int insert(String name, String stat_id, String user_id, String lat, String lng, String zoom) {
        String createTime = TimeUtils.convert(System.currentTimeMillis(), TimeUtils.TIME_FORMAT_YYYY_MM_DD_HH_MM_SS);

        String sql = String.format("INSERT INTO moc_collect(collect_name, lat, lng, zoom, stat_id, user_id, create_time, collect_status)" +
                " VALUES('%s', '%s', '%s', '%s', '%s', '%s', '%s', 0)", name, lat, lng, zoom, stat_id, user_id, createTime);

        return DBHelper.executeInsert(sql);
    }

    public static int delete(String id) {
        String sql = String.format("DELETE FROM moc_collect WHERE id = %s", id);
        return DBHelper.executeUpdate(sql);
    }

    public static List<CollectBean> find(String statId, String userId) {
        String sql = "";
        if (StringUtils.isEmpty(userId)) {
            sql = String.format("SELECT * FROM moc_collect WHERE (stat_id = '%s') " +
                    " AND collect_status = 0 ORDER BY id desc", statId);
        } else {
            sql = String.format("SELECT * FROM moc_collect WHERE (stat_id = '%s' OR user_id = '%s') " +
                    " AND collect_status = 0 ORDER BY id desc", statId, userId);
        }


        SqlResult sr = DBHelper.executeQueryEx(sql);

        if (sr == null || sr.rs == null) {
            return null;
        }

        try {
            List<CollectBean> list = new ArrayList<>();
            while (sr.rs.next()) {
                CollectBean bean = new CollectBean();
                bean.id = sr.rs.getInt("id");
                bean.collect_name = sr.rs.getString("collect_name");
                bean.lat = sr.rs.getString("lat");
                bean.lng = sr.rs.getString("lng");
                bean.zoom = sr.rs.getString("zoom");
                bean.stat_id = sr.rs.getString("stat_id");
                bean.user_id = sr.rs.getString("user_id");
                bean.create_time = sr.rs.getString("create_time");
                bean.collect_status = sr.rs.getInt("collect_status");

                list.add(bean);
            }

            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sr.close();
        }

        return null;
    }

}
